from datetime import datetime
from collections import defaultdict,Counter
from shop import Shop
from sql import sqlseverDB
from config import *
from tool import change_task_status,getTimeStr,get_shopee_rcode
import concurrent.futures as thddd
import requests,logging,math,os,openpyxl,shutil
import sys,time,json,traceback,random,asyncio

taskid=int(sys.argv[1])
tms = sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)
rs_task=tms.ExecQuerydict('select * from task where id=?',(taskid,))
task_info=rs_task[0]
rw_name=task_info['tName']
isMulti=task_info['isMulti']
last_tid=task_info['LastHouTaiID']

logger = logging.getLogger()
logger.setLevel(logging.INFO) 
log_path=f'D:/Web/Gtupdate/GTLOG/{rw_name}{getTimeStr()}.log'
file_handler = logging.FileHandler(log_path, mode='a', encoding='utf-8')
tms.ExecNoQuery('update task set logPath=? where id=?',(log_path,taskid))
console_handler = logging.StreamHandler()
console_handler.setLevel(logging.INFO)
formatter = logging.Formatter('%(asctime)s - %(levelname)s - %(message)s')
file_handler.setFormatter(formatter)
console_handler.setFormatter(formatter)
logger.addHandler(file_handler)
logger.addHandler(console_handler)

def alter_1688sku(rp):
    min_price=159000
    
    for item in rp:

        if not item['pdec']:
            item['pdec'] = item['yntitle']

        if item['pdec'] and len(item['pdec']) > 2500:
            item['pdec'] = item['pdec'][:2500]
        if item['sku_price']<min_price:
            item['sku_price']=min_price
        

    # 处理 sku1 和 sku2 重复的情况
    rp_dict = {}
    for r in rp:
        item_id = r['itemid']
        if rp_dict.get(item_id):
            rp_dict[item_id].append(r)
        else:
            rp_dict[item_id] = [r]
        
    
    for itemid,pskulist in rp_dict.items():
        cn_sku1_to_ynsku1={}
        cn_sku2_to_ynsku2={}

        for pitem in pskulist:
            if pitem['cn_sku1']:
                ynsku = pitem['sku1']
                cn_sku1_to_ynsku1[pitem['cn_sku1']] = ynsku[:17]
            if pitem['cn_sku2']:
                ynsku = pitem['sku2']
                cn_sku2_to_ynsku2[pitem['cn_sku2']] = ynsku[:17]

        for zddd in [cn_sku1_to_ynsku1,cn_sku2_to_ynsku2]:

            ynsku_dict=defaultdict(list)
            for cnsku,ynsku in zddd.items():
                ynsku_dict[ynsku].append((cnsku,ynsku))
            
            for rp_ynsku,skulll in ynsku_dict.items():

                if len(skulll)>1:
                    for jjj in range(len(skulll)):
                        if jjj==0:
                            continue
                        zw_sku=skulll[jjj][0]
                        zddd[zw_sku]=f'{rp_ynsku}_{jjj}'

        piii=0
        min_p=min(pskulist, key=lambda x: x["sku_price"])['sku_price']
        max_p=max(pskulist, key=lambda x: x["sku_price"])['sku_price']
        if max_p/min_p>7:
            min_p=math.ceil(max_p/7)
        for pitem in pskulist:
            if pitem['cn_sku1']:
                pitem['sku1']=cn_sku1_to_ynsku1[pitem['cn_sku1']]
            if pitem['cn_sku2']:
                pitem['sku2']=cn_sku2_to_ynsku2[pitem['cn_sku2']]

            if not pitem['sku1_img']:
                if piii<len(pskulist)-1:
                    pitem['sku1_img']=pskulist[piii+1]['sku1_img']
            if pitem['sku_price']<min_p:
                pitem['sku_price']=min_p
            piii+=1


    final_result = []
    for item_id, records in rp_dict.items():
        grouped_by_sku1 = defaultdict(list)
        cur_sku2s=defaultdict(set)
        for record in records:
            grouped_by_sku1[record['sku1']].append(record)
            if not record['sku2']:
                record['sku2']=''
            cur_sku2s[record['sku1']].add(record['sku2'])
        current_count = 0
        item_group = []
        #logging.info(cur_sku2s.values())
        set_counts = Counter(tuple(sorted(s)) for s in cur_sku2s.values())
        most_common_set, count = set_counts.most_common(1)[0]
        for sku1, sku_records in grouped_by_sku1.items():
            if cur_sku2s[sku1]==set(most_common_set):
                if current_count + len(sku_records) <= 80:
                    item_group.extend(sku_records)
                    current_count += len(sku_records)
                else:
                    break
        final_result.append(item_group)
    #final_result=list(rp_dict.values())
    return final_result

def s1688_to_ynpro(yybm,pc=10,fc=10,kws=[],mbpath='D:/mmmm/document4/阿敏福州广告测品模板.xlsx'):
    total_pcount=pc*fc
    lname_s1688_dict={
        'Product Name':'yntitle',
        'Product Description':'pdec',
        'Parent SKU':'itemid',
        'Variation Integration No.':'itemid',
        'Variation Name1':'sku1_name',
        'Variation Name2':'sku2_name',
        'Option for Variation 1':'sku1',
        'Option for Variation 2':'sku2',
        'Price':'sku_price',
        'Image per Variation':'sku1_img',
        'SKU':'skuid',
        'Cover image':'image',
        'Item Image 1':('images',0),
        'Item Image 2':('images',1),
        'Item Image 3':('images',2),
        'Item Image 4':('images',3),
        'Item Image 5':('images',4),
        'Item Image 6':('images',5),
        'Item Image 7':('images',6),
        'Item Image 8':('images',7)
        
    }

    lname_gd_dict={
        'Stock':'100',
        'Weight':'10',
        'Reguler (Cashless)':'On'
    }
    yybm_filter_str=''
    if yybm=='zyd618':
        yybm_filter_str='AND price>=40 AND historical_sold>=2000'
    elif yybm=='cym789' or yybm=='wzm123':
        yybm_filter_str='AND price<=60 and rating_star>=4 AND historical_sold between 1000 and 2000'

    if kws:
        kw_str=','.join([f"'{kw}'"  for kw in kws])
        wstr_kws=f'AND from_keywords in ({kw_str})'
        sqlstr=f'''SELECT 
                    sp.itemid,
                    sp.yntitle + ' |' + sp.random_code + '|' AS yntitle,
                    sp.sku1_name,
                    sp.sku2_name,
                    sp.image,
                    sp.images,
                    sk.sku1_img,
                    sk.sku1 AS cn_sku1,
                    sk.sku2 AS cn_sku2,
                    sk.ynsku1 AS sku1,
                    sk.ynsku2 AS sku2,
                    sk.skuid,
                    sku_price AS cn_price,
                    cast(sk.sku_price * 1.7 * 2250 as int) AS sku_price,
                    sp.yndes AS pdec
                FROM 
                    (  SELECT TOP {total_pcount} itemid FROM S1688Pro 
                        WHERE is_yn_ad = 0 
                        AND is_yn_dc = 0 
                        AND is_fy_sku=1 
                        AND KW_YYBM='{yybm}' 
                        AND is_yntit=1
                        And yntitle not like '%Fjallraven%'
                        AND yntitle not like '%GLORY%'
                        AND yntitle not like '%UFC%'
                        AND yntitle not like '%crocs%'
                        AND yntitle not like '%Vichy%'
                        AND yntitle not like '%SHISEIDO%'
                        AND shujuzhuangtai= '可广告测品'
                        AND shop_name like '%公司%'
                        {yybm_filter_str}
                        {wstr_kws}
                    ) AS temp
                JOIN 
                    S1688Pro sp ON sp.itemid = temp.itemid
                JOIN 
                    S1688ProSKU sk ON sp.itemid = sk.itemid;
                '''
    
    else:
        sqlstr=f'''
            WITH Base AS (
                SELECT 
                    itemid,
                    from_keywords,
                    ROW_NUMBER() OVER (PARTITION BY from_keywords ORDER BY itemid) AS rn
                FROM S1688Pro
                WHERE is_yn_ad = 0 
                    AND is_yn_dc = 0 
                    AND is_fy_sku = 1 
                    AND KW_YYBM ='{yybm}'
                    AND is_yntit = 1
                    AND yntitle not like '%Fjallraven%'
                    AND yntitle not like '%GLORY%'
                    AND yntitle not like '%UFC%'
                    AND yntitle not like '%crocs%'
                    AND yntitle not like '%Vichy%'
                    AND yntitle not like '%SHISEIDO%'
                    AND shujuzhuangtai = '可广告测品'
                    AND shop_name like '%公司%'
                    {yybm_filter_str}.
            ),
            Ordered AS (
                SELECT *,
                    DENSE_RANK() OVER (ORDER BY from_keywords) AS kw_order
                FROM Base
            )
            SELECT 
                    sp.itemid,
                    sp.yntitle + ' |' + sp.random_code + '|' AS yntitle,
                    sp.sku1_name,
                    sp.sku2_name,
                    sp.image,
                    sp.images,
                    sk.sku1_img,
                    sk.sku1 AS cn_sku1,
                    sk.sku2 AS cn_sku2,
                    sk.ynsku1 AS sku1,
                    sk.ynsku2 AS sku2,
                    sk.skuid,
                    sku_price AS cn_price,
                    cast(sk.sku_price * 1.7 * 2250 as int) AS sku_price,
                    sp.yndes AS pdec
                FROM 
                    (SELECT TOP {total_pcount} itemid, from_keywords
                        FROM Ordered
                        ORDER BY rn, kw_order) as temp
                JOIN 
                    S1688Pro sp ON sp.itemid = temp.itemid
                JOIN 
                    S1688ProSKU sk ON sp.itemid = sk.itemid;
        '''

    
    rp=tms.ExecQuerydict(sqlstr)
    if len(rp)==0:
        return '无产品'
    # lname_gd_dict['Stock']=0
    # rp=tms.ExecQuerydict(sqlstr2)
    dcmr=f'1688导出/{yybm}_1688TOYN_{getTimeStr()}'
    os.mkdir(dcmr)
    smt_rp=alter_1688sku(rp)

    for fj in range(fc):
        dc_itemids=[]
        cur_smt_rp=smt_rp[fj*pc:(fj+1)*pc]
        if len(cur_smt_rp)==0:
            break
        wb=openpyxl.load_workbook(mbpath)
        ws=wb['Template']
        mb_tits=[ws.cell(3,col).value for col in range(1,ws.max_column+1)]
        row=7
        pskudatas=[]
        for pii_lsit in cur_smt_rp:
            pskudatas.extend(pii_lsit)
            dc_itemids.append(pii_lsit[0]['itemid'])
        allimgs=[]
        pskudatas.sort(key=lambda x: (x['itemid'],x['sku1'], x['sku2']))
        for pro in pskudatas:
            if pro['images']:
                try:
                    allimgs=json.loads(pro['images'])
                except Exception as e:
                    pass
            
            try:
                for tit_col_ind in range(len(mb_tits)):
                    rol=tit_col_ind+1
                    lname=mb_tits[tit_col_ind]


                    if lname in lname_s1688_dict:
                        czddd=lname_s1688_dict[lname]
                        if isinstance(czddd,str):
                            ws.cell(row=row,column=rol).value=pro[czddd]
                        else:
                            ccc_ind=czddd[1]
                            if ccc_ind<len(allimgs):
                                ws.cell(row=row,column=rol).value=allimgs[ccc_ind]

                    elif lname in lname_gd_dict:
                        if lname=='Stock':
                            if pro['cn_price']<15 or pro['cn_price']>60:
                                ws.cell(row=row,column=rol).value=0
                            else:
                                ws.cell(row=row,column=rol).value=lname_gd_dict[lname]
                        
                        else:
                            ws.cell(row=row,column=rol).value=lname_gd_dict[lname]

                row+=1
            except Exception as e:
                logging.info(f'第{row}行,第{rol}列填写错误 =>{e}')
        ex_path=f'{dcmr}/{yybm}_第{fj+1}份{len(cur_smt_rp)}个_1688TOYN.xlsx'
        wb.save(ex_path)
        wstr=','.join([f"'{dc_itemid}'" for dc_itemid in dc_itemids])
        aff_dc=tms.ExecNoQuery(f'update S1688Pro set is_yn_dc=1 where itemid in ({wstr})')
        logging.info(f'{yybm},第 {fj+1} 份文件成功导出{len(cur_smt_rp)}个产品,到:《{ex_path}》,标记 {aff_dc} 个状态is_yn_dc=1')
       

    return dcmr

def zip_folder(ex_path, yybm,file_name):
    # 绝对目标目录（注意：路径中的反斜杠可用原始字符串 r"" 或者替换为 /）
    output_dir = f"D:/mmmm/1688导出/{yybm}"
    os.makedirs(output_dir, exist_ok=True)  # 如果不存在则创建

    # 输出压缩文件的完整路径（不加 .zip 后缀，shutil 会自动添加）
    zip_file_path = os.path.join(output_dir, file_name)

    # 创建压缩包（zip 格式）
    shutil.make_archive(zip_file_path, 'zip', ex_path)

    logging.info(f"已成功将 {ex_path} 打包至 {zip_file_path}.zip")


def main():
    rs=tms.ExecQuerydict('select * from taskDC where status=0 order by start_time')
    task_count=len(rs)
    if task_count==0:
        logging.info('当前无导出任务需执行')
        tms.ExecNoQuery('update [task] set state=0,tcount=0,LastHouTaiID=null where [id]=?',(taskid,))
        change_task_status(taskid,0)
        return
    try:
        for r in rs:
            dc_id=r['id']
            cur_yybm=r['YYBM']
            t_start_time=r['start_time'].strftime('%Y-%m-%d_%H-%M-%S')
            per_count=r['per_count']
            file_count=r['file_count']
            kws_str=r['kws']
            bid=r['bid']
            aff0=tms.ExecNoQuery('update taskDC set status=1 where id=?',(dc_id,))
            logging.info(f'{cur_yybm},任务时间:{t_start_time},开始导出,更改状态:{aff0}')

            if bid:
                sql_str1=f"select * from  houtai where BrowserID='{bid}'"
            else:
                sql_str1=f"select * from  houtai where isAddKw=1 and is_task_baned=0 and dpname not like '%假期模式%' and dpname not like '%休假模式%' and SJBM='{cur_yybm}' "
            sql_str2=f"select * from  houtai where isAddKw=1 and is_task_baned=0 and dpname not like '%假期模式%' and dpname not like '%休假模式%'"
            rs_dp=tms.ExecQuerydict(sql_str1)
            if len(rs_dp)==0:
                rs_dp=tms.ExecQuerydict(sql_str2)
            while True:
                if len(rs_dp)==1:
                    dpinfo=rs_dp[0]
                else:
                    dpinfo=random.choice(rs_dp)
                cur_shop=Shop(dpinfo)
                cur_shop.task_id=11
                res_mbpath=asyncio.run(cur_shop.download_basic_temp())
                if res_mbpath['code']==0:
                    mbpath=res_mbpath['path']
                    break
            
            kws=[]
            if kws_str:
                kws=kws_str.split('|')
            
            ex_path=s1688_to_ynpro(cur_yybm,per_count,file_count,kws,mbpath)

            if ex_path=='无产品':
                file_name=None
                download_url='暂时没有符合条件的产品可导出'
            else:
                file_name=f'{cur_yybm}_{t_start_time}'
                zip_folder(ex_path,cur_yybm,file_name)
                download_url=f'http://198.12.127.43/1688导出/{cur_yybm}/{file_name}.zip'

            cur_time=datetime.now()
            aff=tms.ExecNoQuery('update taskDC set status=2,download_url=?,end_time=?,file_name=? where id=?',(download_url,cur_time,file_name,dc_id))
            task_count-=1
            logging.info(f'{cur_yybm},关键词:《{kws}》任务时间:{t_start_time},导出链接:{download_url},更改状态:{aff},剩余导出任务:{task_count}')
    except Exception as e:
        print(e)
        traceback.print_exc()
    
                
            
        
    tms.ExecNoQuery('update [task] set state=0,tcount=0,LastHouTaiID=null where [id]=?',(taskid,))
    change_task_status(taskid,0)

    aff_zz=tms.ExecNoQuery('update TaskDC set status=0 where status=1')

    logging.info(f'重置 {aff_zz} 个导出任务')

if __name__=='__main__':

    main()


